• Eviter de changer votre répertoire de travail avec setwd(),
  c.-à-d. démarrer R directement au bon endroit ou définir un "Projet" dans Rstudio.
• Ne pas utiliser le .Rprofile avec des options modifiant le comportant de R.
• Désactiver la conversion automatique des chaînes de caractères en facteurs options(stringsAsFactors = FALSE).
• Ne pas utiliser rm(list = ls()) pour "rafraichir" votre session.
• Ne pas sauvegarder/charger .Rdata en quittant/démarrant votre session R.

Un ensemble d'outils basés sur une philosophie/grammaire commune.

if (!"tidyverse"%in%rownames(installed.packages())) {
  install.packages("tidyverse")
}
library(tidyverse)

library(tidyverse) chargera les packages :

• readr, importation.
• tibble, classe d'objet "tibble".
• dplyr, manipulation.
• tidyr, mise en forme.
• ggplot2, visualisation.
• purrr, programmation.

Ces packages représentent la base du tidyverse et sont en constante évolution.

tidyverse_update()

library(tidyverse)

tidyverse_conflicts()

#> -- Conflicts ------------------------------------ tidyverse_conflicts() --
#> x readr::col_factor() masks scales::col_factor()
#> x purrr::discard()    masks scales::discard()
#> x dplyr::filter()     masks stats::filter()
#> x dplyr::lag()        masks stats::lag()
#> x dplyr::src()        masks Hmisc::src()
#> x dplyr::summarize()  masks Hmisc::summarize()

tibble(
  x = 1:5, 
  y = 1, 
  z = x ^ 2 + y
)

#> # A tibble: 5 x 3
#>       x     y     z
#>   <int> <dbl> <dbl>
#> 1     1  1.00  2.00
#> 2     2  1.00  5.00
#> 3     3  1.00 10.0 
#> # ... with 2 more rows

data.frame(
  x = 1:5, 
  y = 1, 
  z = x ^ 2 + y
)

#> Error in data.frame(x = 1:5, y = 1, z = x^2 + y): objet 'x' introuvable

• Et avec des noms de variables "exotiques" ?

  data.frame(`1`= 1:3)

  #>   X1
  #> 1  1
  #> 2  2
  #> 3  3

  tibble(
    `;)` = 1:5, 
    `42` = "1", 
    `€` = `;)` ^ 2 + as.numeric(`42`)
  )

  #> # A tibble: 5 x 3
  #>    `;)` `42`    `€`
  #>   <int> <chr> <dbl>
  #> 1     1 1      2.00
  #> 2     2 1      5.00
  #> 3     3 1     10.0 
  #> # ... with 2 more rows

• Les méthodes show() et print().

  as.data.frame(mtcars)

  #>                      mpg cyl  disp  hp drat    wt  qsec vs am gear carb
  #> Mazda RX4           21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4
  #> Mazda RX4 Wag       21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4
  #> Datsun 710          22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1
  #> Hornet 4 Drive      21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1
  #> Hornet Sportabout   18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2
  #> Valiant             18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1
  #> Duster 360          14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4
  #> Merc 240D           24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
  #> Merc 230            22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2
  #> Merc 280            19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4
  #> Merc 280C           17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4
  #> Merc 450SE          16.4   8 275.8 180 3.07 4.070 17.40  0  0    3    3
  #> Merc 450SL          17.3   8 275.8 180 3.07 3.730 17.60  0  0    3    3
  #> Merc 450SLC         15.2   8 275.8 180 3.07 3.780 18.00  0  0    3    3
  #> Cadillac Fleetwood  10.4   8 472.0 205 2.93 5.250 17.98  0  0    3    4
  #> Lincoln Continental 10.4   8 460.0 215 3.00 5.424 17.82  0  0    3    4
  #> Chrysler Imperial   14.7   8 440.0 230 3.23 5.345 17.42  0  0    3    4
  #> Fiat 128            32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1
  #> Honda Civic         30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2
  #> Toyota Corolla      33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1
  #> Toyota Corona       21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1
  #> Dodge Challenger    15.5   8 318.0 150 2.76 3.520 16.87  0  0    3    2
  #> AMC Javelin         15.2   8 304.0 150 3.15 3.435 17.30  0  0    3    2
  #> Camaro Z28          13.3   8 350.0 245 3.73 3.840 15.41  0  0    3    4
  #> Pontiac Firebird    19.2   8 400.0 175 3.08 3.845 17.05  0  0    3    2
  #> Fiat X1-9           27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1
  #> Porsche 914-2       26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2
  #> Lotus Europa        30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2
  #> Ford Pantera L      15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4
  #> Ferrari Dino        19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6
  #> Maserati Bora       15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8
  #> Volvo 142E          21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2

  print(as.data.frame(mtcars))

  #>                      mpg cyl  disp  hp drat    wt  qsec vs am gear carb
  #> Mazda RX4           21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4
  #> Mazda RX4 Wag       21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4
  #> Datsun 710          22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1
  #> Hornet 4 Drive      21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1
  #> Hornet Sportabout   18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2
  #> Valiant             18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1
  #> Duster 360          14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4
  #> Merc 240D           24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
  #> Merc 230            22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2
  #> Merc 280            19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4
  #> Merc 280C           17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4
  #> Merc 450SE          16.4   8 275.8 180 3.07 4.070 17.40  0  0    3    3
  #> Merc 450SL          17.3   8 275.8 180 3.07 3.730 17.60  0  0    3    3
  #> Merc 450SLC         15.2   8 275.8 180 3.07 3.780 18.00  0  0    3    3
  #> Cadillac Fleetwood  10.4   8 472.0 205 2.93 5.250 17.98  0  0    3    4
  #> Lincoln Continental 10.4   8 460.0 215 3.00 5.424 17.82  0  0    3    4
  #> Chrysler Imperial   14.7   8 440.0 230 3.23 5.345 17.42  0  0    3    4
  #> Fiat 128            32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1
  #> Honda Civic         30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2
  #> Toyota Corolla      33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1
  #> Toyota Corona       21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1
  #> Dodge Challenger    15.5   8 318.0 150 2.76 3.520 16.87  0  0    3    2
  #> AMC Javelin         15.2   8 304.0 150 3.15 3.435 17.30  0  0    3    2
  #> Camaro Z28          13.3   8 350.0 245 3.73 3.840 15.41  0  0    3    4
  #> Pontiac Firebird    19.2   8 400.0 175 3.08 3.845 17.05  0  0    3    2
  #> Fiat X1-9           27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1
  #> Porsche 914-2       26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2
  #> Lotus Europa        30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2
  #> Ford Pantera L      15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4
  #> Ferrari Dino        19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6
  #> Maserati Bora       15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8
  #> Volvo 142E          21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2

• Les méthodes show() et print() .

  as_tibble(mtcars)

  #> # A tibble: 32 x 11
  #>     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
  #> * <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
  #> 1  21.0  6.00   160 110    3.90  2.62  16.5  0     1.00  4.00  4.00
  #> 2  21.0  6.00   160 110    3.90  2.88  17.0  0     1.00  4.00  4.00
  #> 3  22.8  4.00   108  93.0  3.85  2.32  18.6  1.00  1.00  4.00  1.00
  #> # ... with 29 more rows

  print(as_tibble(mtcars))

  #> # A tibble: 32 x 11
  #>     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
  #> * <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
  #> 1  21.0  6.00   160 110    3.90  2.62  16.5  0     1.00  4.00  4.00
  #> 2  21.0  6.00   160 110    3.90  2.88  17.0  0     1.00  4.00  4.00
  #> 3  22.8  4.00   108  93.0  3.85  2.32  18.6  1.00  1.00  4.00  1.00
  #> # ... with 29 more rows

• La fonction rownames_to_column() (et column_to_rownames()).

  as_tibble(rownames_to_column(mtcars))

  #> # A tibble: 32 x 12
  #>   rowname       mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
  #>   <chr>       <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
  #> 1 Mazda RX4    21.0  6.00   160 110    3.90  2.62  16.5  0     1.00  4.00  4.00
  #> 2 Mazda RX4 ~  21.0  6.00   160 110    3.90  2.88  17.0  0     1.00  4.00  4.00
  #> 3 Datsun 710   22.8  4.00   108  93.0  3.85  2.32  18.6  1.00  1.00  4.00  1.00
  #> # ... with 29 more rows

  print(as_tibble(rownames_to_column(mtcars)))

  #> # A tibble: 32 x 12
  #>   rowname       mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
  #>   <chr>       <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
  #> 1 Mazda RX4    21.0  6.00   160 110    3.90  2.62  16.5  0     1.00  4.00  4.00
  #> 2 Mazda RX4 ~  21.0  6.00   160 110    3.90  2.88  17.0  0     1.00  4.00  4.00
  #> 3 Datsun 710   22.8  4.00   108  93.0  3.85  2.32  18.6  1.00  1.00  4.00  1.00
  #> # ... with 29 more rows

Changer les options d'affichages d'un tibble :

• via options()

  options(tibble.print_max = n, tibble.print_min = m, dplyr.print_min = p)

  Afficher les n premières lignes, s'il y a plus de m lignes et sur p colonnes.

• via print()

  print(x = DF, n = n, width = p))

• dans Rstudio via View()

  View(mtcars)

mtcars$mpg

#>  [1] 21.0 21.0 22.8 21.4 18.7 18.1 14.3 24.4 22.8 19.2 17.8 16.4 17.3 15.2 10.4
#> [16] 10.4 14.7 32.4 30.4 33.9 21.5 15.5 15.2 13.3 19.2 27.3 26.0 30.4 15.8 19.7
#> [31] 15.0 21.4

mtcars[["mpg"]]

#>  [1] 21.0 21.0 22.8 21.4 18.7 18.1 14.3 24.4 22.8 19.2 17.8 16.4 17.3 15.2 10.4
#> [16] 10.4 14.7 32.4 30.4 33.9 21.5 15.5 15.2 13.3 19.2 27.3 26.0 30.4 15.8 19.7
#> [31] 15.0 21.4

mtcars[, "mpg"]

#>  [1] 21.0 21.0 22.8 21.4 18.7 18.1 14.3 24.4 22.8 19.2 17.8 16.4 17.3 15.2 10.4
#> [16] 10.4 14.7 32.4 30.4 33.9 21.5 15.5 15.2 13.3 19.2 27.3 26.0 30.4 15.8 19.7
#> [31] 15.0 21.4

as_tibble(mtcars)$mpg

#>  [1] 21.0 21.0 22.8 21.4 18.7 18.1 14.3 24.4 22.8 19.2 17.8 16.4 17.3 15.2 10.4
#> [16] 10.4 14.7 32.4 30.4 33.9 21.5 15.5 15.2 13.3 19.2 27.3 26.0 30.4 15.8 19.7
#> [31] 15.0 21.4

as_tibble(mtcars)[["mpg"]]

#>  [1] 21.0 21.0 22.8 21.4 18.7 18.1 14.3 24.4 22.8 19.2 17.8 16.4 17.3 15.2 10.4
#> [16] 10.4 14.7 32.4 30.4 33.9 21.5 15.5 15.2 13.3 19.2 27.3 26.0 30.4 15.8 19.7
#> [31] 15.0 21.4

as_tibble(mtcars)[, "mpg"]

#> # A tibble: 32 x 1
#>     mpg
#>   <dbl>
#> 1  21.0
#> 2  21.0
#> 3  22.8
#> # ... with 29 more rows

De "vielles" fonctions limitées à la classe d'objet data.frame !

tb <- as_tibble(mtcars)
class(tb)

#> [1] "tbl_df"     "tbl"        "data.frame"

class(as.data.frame(tb))

#> [1] "data.frame"

• Comment savoir si un objet est de classe tibble, data.frame ou encore matrix ?

• Comment se comporte les opérations suivantes sur un data.frame et l'équivalent tibble ?
  Quelle est la différence ?

  df <- data.frame(abc = 1, xyz = "a")
  df$xyz
  df[["xyz"]]
  df[, "xyz"]
  df[, c("abc", "xyz")]

• Comment extraire une colonne en utilisant une variable ?

  x <- "abc"

• Comment savoir si un objet est de classe tibble, data.frame ou encore matrix ?

  class(dta)

• Comment se comporte les opérations suivantes sur un data.frame et l'équivalent tibble ?
  Quelle est la différence ?

  df1 <- data.frame(abc = 1, xyz = "a")

  df2 <- as_tibble(df1)

  df1$xyz
  df1[["xyz"]]
  df1[, "xyz"]
  df1[, c("abc", "xyz")]

• Comment extraire une colonne en utilisant une variable ?

  x <- "abc"
  df1[, x]
  df1[[x]]

  x <- "abc"
  df2[, x]
  df2[[x]]

Manipuler des noms de variables non-conventionnelles :

dta <- tibble(
  `1` = 1:10,
  `2` = `1` * 2 + rnorm(length(`1`))
)

• Récupérez la variable nommée 1.
• Tracez un nuage de points de 1 par rapport à 2.
• Créez un nouveau tibble avec une colonne nommée 3, contenant le résultat de 2 divisé par 1.
• Renommez les colonnes avec leurs noms littéral: "un", "deux" et "trois".

Manipuler des noms de variables non-conventionnelles :

dta <- tibble(
  `1` = 1:10,
  `2` = `1` * 2 + rnorm(length(`1`))
)

• Récupérez la variable nommée 1.

  dta$`1`

• Tracez un nuage de points de 1 par rapport à 2.

  plot(dta$`1`, dta$`2`)
  ggplot(data = dta, aes(x = `1`, y = `2`)) + geom_point(colour ="white")

• Créez un nouveau tibble avec une colonne nommée 3, contenant le résultat de 2 divisé par 1.

  dta <- tibble(
    `1` = 1:10,
    `2` = `1` * 2 + rnorm(length(`1`)),
    `3` = `2`/ `1`
  )

• Renommez les colonnes avec leurs noms littéral : un, deux et trois, en construisant un nouveau tibble.

  dta <- tibble(
    "un" = 1:10,
    "deux" = un * 2 + rnorm(length(un)),
    "trois" = deux / un
  )

• Cheatsheets Rstudio : https://www.rstudio.com/resources/cheatsheets/

vignette(package = "tibble")

library(pryr)

library(tidyverse)

tidyverse_conflicts()

#> -- Conflicts ------------------------------------ tidyverse_conflicts() --
#> x readr::col_factor() masks scales::col_factor()
#> x purrr::discard()    masks scales::discard()
#> x dplyr::filter()     masks stats::filter()
#> x dplyr::lag()        masks stats::lag()
#> x pryr::partial()     masks purrr::partial()
#> x dplyr::src()        masks Hmisc::src()
#> x dplyr::summarize()  masks Hmisc::summarize()

L'opérateur "pipe" (%>%) provient du package magrittr développé par Stefan Milton Bache.

Par défaut library(tidyverse) s'occupe de charger l'opérateur "pipe".

Exemple :

• x %>% f équivalent à f(x)
• x %>% f(y) équivalent à f(x, y)
• x %>% f %>% g %>% h équivalent à h(g(f(x)))
• f <- . %>% cos %>% sin équivalent à f <- function(.) sin(cos(.))

Une aide à :

• l'écriture du code.
• la lisibilité du code.

Note : Raccourci Ctrl+M dans Rstudio pour faire apparaître un "pipe".

x <- rnorm(25)
x1 <- sqrt(x)
x2 <- na.exclude(x1)
x3 <- x2<1
x4 <- table(x3)
x5 <- prop.table(x4)
x6 <- which.max(x5)
x7 <- names(x6)

Les inconvénients de cette écriture :

• Obligation de nommer de façon explicite les différents objets.
• Environnement surchargé d'objets peu utiles (ls()).

• Utilisation plus importante de la mémoire vive ?

  mtcars2 <- mtcars %>% mutate(cyl_fac = factor(cyl))
  
  pryr::object_size(mtcars)
  pryr::object_size(mtcars2)
  pryr::object_size(mtcars, mtcars2)

x <- rnorm(25)

x <- sqrt(x)
x <- na.exclude(x)
x <- x<1
x <- table(x)
x <- prop.table(x)
x <- which.max(x)
x <- names(x)

Les inconvénients de cette écriture :

• Obligation de relancer tout le code pour débuguer.
• Répétition du nom de l'objet (14 fois).

x <- rnorm(25)

names(
  which.max(
    prop.table(
      table(
        na.exclude(
          sqrt(x)
        )<1
      )
    )
  )
)

names(which.max(prop.table(table(na.exclude(sqrt(x))<1))))

Les inconvénients de cette écriture :

• L'ordre des appels va de l'intérieur vers l'extérieur.
• Les arguments peuvent se situer "loin" de la fonction.

x %>% 
  sqrt() %>% 
  na.exclude() %>% 
  `<`(1) %>% 
  table() %>% 
  prop.table() %>% 
  which.max() %>% 
  names()

A éviter si :

• le nombre d'étapes devient important.
  Utiliser des objets intermédiaires avec des noms claires.
• la séquence d'opération n'est pas linéaire.
• les opérations nécéssitent plusieurs objets en entrée et/ou sortie.

vignette(package = "magrittr")

library(readxl)

library(tidyverse)

tidyverse_conflicts()

#> -- Conflicts ------------------------------------ tidyverse_conflicts() --
#> x readr::col_factor() masks scales::col_factor()
#> x purrr::discard()    masks scales::discard()
#> x dplyr::filter()     masks stats::filter()
#> x dplyr::lag()        masks stats::lag()
#> x pryr::partial()     masks purrr::partial()
#> x dplyr::src()        masks Hmisc::src()
#> x dplyr::summarize()  masks Hmisc::summarize()

Lire des tableaux rectangulaires :

• read_csv() : fichier avec séparateur virgule (".csv").
• read_tsv() : fichier avec séparateur tabulation.
• read_table() : fichier avec séparateur espace.
• read_delim() : forme générale (delim = "").

Lire des données R (".rds") :

• read_rds() : surcouche de readRDS() (sans compression).

Lire des formats plus exotiques :

• read_fwf() : fichier à largeur fixe (nombre de caractères).
• read_log() : fichier de log provenant de serveur web Apache.

• Nom homogène des fonctions et arguments.
• Plus rapide (moins rapide que data.table).
• Auto-conversion en facteur.
• L'importation ne dépend pas des variables locales.

locale()

#> <locale>
#> Numbers:  123,456.78
#> Formats:  %AD / %AT
#> Timezone: UTC
#> Encoding: UTF-8
#> <date_names>
#> Days:   Sunday (Sun), Monday (Mon), Tuesday (Tue), Wednesday (Wed), Thursday
#>         (Thu), Friday (Fri), Saturday (Sat)
#> Months: January (Jan), February (Feb), March (Mar), April (Apr), May (May),
#>         June (Jun), July (Jul), August (Aug), September (Sep), October
#>         (Oct), November (Nov), December (Dec)
#> AM/PM:  AM/PM

• Conversion des champs "date" au format "%Y-%m-%d".
• Conversion des champs date/heure dans la norme ISO8601.
  Par exemple :
  • 07:57:59Z
  • 2018-04-13T07:57:59Z
• Noms des colonnes inchangés.
• Renvoie un objet tibble.
• Pas de nom de ligne.

ratings <- read.csv(
  file = "./materials/ratings.csv"
)
str(ratings[0, ])

#> 'data.frame':    0 obs. of  13 variables:
#>  $ Const         : chr 
#>  $ Your.Rating   : int 
#>  $ Date.Rated    : chr 
#>  $ Title         : chr 
#>  $ URL           : chr 
#>  $ Title.Type    : chr 
#>  $ IMDb.Rating   : num 
#>  $ Runtime..mins.: int 
#>  $ Year          : int 
#>  $ Genres        : chr 
#>  $ Num.Votes     : int 
#>  $ Release.Date  : chr 
#>  $ Directors     : chr

ratings <- read_csv(
  file = "./materials/ratings.csv"
)

#> Parsed with column specification:
#> cols(
#>   Const = col_character(),
#>   `Your Rating` = col_integer(),
#>   `Date Rated` = col_date(format = ""),
#>   Title = col_character(),
#>   URL = col_character(),
#>   `Title Type` = col_character(),
#>   `IMDb Rating` = col_double(),
#>   `Runtime (mins)` = col_integer(),
#>   Year = col_integer(),
#>   Genres = col_character(),
#>   `Num Votes` = col_integer(),
#>   `Release Date` = col_date(format = ""),
#>   Directors = col_character()
#> )

read.csv(
  text = "a,b,c
    1,2,3
    4,5,6"
)

#>   a b c
#> 1 1 2 3
#> 2 4 5 6

read_csv(
  "a,b,c
  1,2,3
  4,5,6"
)

#> # A tibble: 2 x 3
#>       a     b     c
#>   <int> <int> <int>
#> 1     1     2     3
#> 2     4     5     6

• L'argument skip = n.

  read_csv("Une ligne de métadonnées
    x,y,z
    1,2,3", skip = 1)

  #> # A tibble: 1 x 3
  #>       x     y     z
  #>   <int> <int> <int>
  #> 1     1     2     3

• L'argument comment = "#".

  read_csv("# Une ligne de commentaires
    x,y,z
    1,2,3", comment = "#")

  #> # A tibble: 1 x 3
  #>       x     y     z
  #>   <int> <int> <int>
  #> 1     1     2     3

• L'argument col_names = FALSE.

  read_csv("1,2,3\n4,5,6", col_names = FALSE)

  #> # A tibble: 2 x 3
  #>      X1    X2    X3
  #>   <int> <int> <int>
  #> 1     1     2     3
  #> 2     4     5     6

• Utiliser col_names pour nommer les colonnes.

  read_csv("1,2,3\n4,5,6", col_names = c("a", "b", "c"))

  #> # A tibble: 2 x 3
  #>       a     b     c
  #>   <int> <int> <int>
  #> 1     1     2     3
  #> 2     4     5     6

• L'argument na = ".".

  read_csv("a,b,c\n1,2,3\n4,.,6", na = ".")

  #> # A tibble: 2 x 3
  #>       a     b     c
  #>   <int> <int> <int>
  #> 1     1     2     3
  #> 2     4    NA     6

• L'argument col_types.

  read_csv(
    "a,b,c\n1,x,2018-05-03\n4,y,2018-05-04", 
    col_types = list(col_integer(), col_character(), col_date(format = "%Y-%m-%d"))
  )

  #> # A tibble: 2 x 3
  #>       a b     c         
  #>   <int> <chr> <date>    
  #> 1     1 x     2018-05-03
  #> 2     4 y     2018-05-04

• Quelle fonction utiliseriez-vous pour importer un document avec | en séparateur ?

• Un fichier CSV peut parfois contenir une virgule dans un champ.
  Astuce : utilisez des guillemets simple ' ou double ".
  1. Quel est le caractère par défaut utiliser par read_csv() ?
     
  2. Quelle fonction et argument utiliser pour changer ce caractère ?
  3. Quelle fonction et avec quelle valeur argument liriez-vous la chaîne suivante ?

  "x,y\n1,'a,b'"

• Identifiez les problèmes des lignes suivantes.

  read_csv("a,b\n1,2,3\n4,5,6")
  read_csv("a,b,c\n1,2\n1,2,3,4")
  read_csv("a,b\n\"1")
  read_csv("a,b\n1,2\na,b")
  read_csv("a;b\n1;3")

• Quelle fonction utiliseriez-vous pour importer un document avec | en séparateur ?

  read_delim(file = path, delim = "|")

• Un fichier CSV peut parfois contenir une virgule dans un champ.
  Astuce : utilisez des guillemets simple ' ou double ".
  1. Quel est le caractère par défaut utiliser par read_csv() ?
  r "
  1. Quelle fonction et argument utiliser pour changer ce caractère ?
  r quote = "\""
  1. Quelle fonction et avec quelle valeur argument liriez-vous la chaîne suivante ?

  read_csv("x,y\n1,'a,b'", quote = "\'")
  read_delim("x,y\n1,'a,b'", delim = ",", quote = "\'")

• Identifiez les problèmes des lignes suivantes.

  read_csv("a,b\n1,2,3\n4,5,6")
  read_csv("a,b,c\n1,2\n1,2,3,4")
  read_csv("a,b\n\"1")
  read_csv("a,b\n1,2\na,b")
  read_csv("a;b\n1;3")

readr inclut également des fonctions d'écriture write_*().

• Les chaînes de caractères sont en UTF-8.
• Les dates et date/heure sont au format ISO8601.

Exemple :

readr::write_csv(x = ratings, path = "ratings.csv")

writexl::write_xlsx(x = ratings, path = "ratings.xlsx")

library(tidyverse)

tidyverse_conflicts()

#> -- Conflicts ------------------------------------ tidyverse_conflicts() --
#> x readr::col_factor() masks scales::col_factor()
#> x purrr::discard()    masks scales::discard()
#> x dplyr::filter()     masks stats::filter()
#> x dplyr::lag()        masks stats::lag()
#> x pryr::partial()     masks purrr::partial()
#> x dplyr::src()        masks Hmisc::src()
#> x dplyr::summarize()  masks Hmisc::summarize()

5 (+1) fonctions clés de dplyr :

1. Sélectionner des observations par valeur (filter()).
2. Trier les lignes (arrange()).
3. Sélectionner des colonnes (select()).
4. Ajouter des colonnes en fonction des colonnes préexistantes (mutate()).

5. Résumer des valeurs (summarise()).

6. group_by() pour travailler sur des sous-ensembles d'un jeu de données.

Une syntaxe commune :

• le premier argument est un objet data.frame.
• Le second argument décrit l'action à effectuer (noms de variable sans guillemets).
• Renvoie un data.frame (ou tibble).

ratings <- read_csv(file = "./materials/ratings.csv")

filter(ratings, `Your Rating` >= 9, `IMDb Rating` >= 9)

#> # A tibble: 1 x 13
#>   Const  `Your Rating` `Date Rated` Title   URL      `Title Type` `IMDb Rating`
#>   <chr>          <int> <date>       <chr>   <chr>    <chr>                <dbl>
#> 1 tt011~             9 2014-11-08   The Sh~ https:/~ movie                 9.30
#> # ... with 6 more variables: `Runtime (mins)` <int>, Year <int>, Genres <chr>,
#> #   `Num Votes` <int>, `Release Date` <date>, Directors <chr>

ratings %>% 
  filter(`Your Rating` >= 9 & `IMDb Rating` >= 9)

#> # A tibble: 1 x 13
#>   Const  `Your Rating` `Date Rated` Title   URL      `Title Type` `IMDb Rating`
#>   <chr>          <int> <date>       <chr>   <chr>    <chr>                <dbl>
#> 1 tt011~             9 2014-11-08   The Sh~ https:/~ movie                 9.30
#> # ... with 6 more variables: `Runtime (mins)` <int>, Year <int>, Genres <chr>,
#> #   `Num Votes` <int>, `Release Date` <date>, Directors <chr>

• Trouvez le film le plus noté.

• Trouvez les films contenant "shark" dans le titre (grepl()).

• Trouvez les films vus entre le 3 Mai 2018 et le 25 Mai 2018.

• Que fait la fonction between() ?

• Trouvez le film le plus noté.

  ratings %>% 
    filter(`Num Votes`==max(`Num Votes`))

• Trouvez les films contenant "shark" dans le titre (grepl()).

  ratings %>% 
    filter(grepl(pattern = "shark", x = Title, ignore.case = TRUE))

• Trouvez les films vus entre le 3 Mai 2018 et le 25 Mai 2018.

  ratings %>% 
    filter(`Date Rated`>="2018-04-25" & `Date Rated`<="2018-05-03")

• Que fait la fonction between() ?

  ratings %>% 
    filter(between(`Date Rated`, "2018-04-25", "2018-05-03"))

ratings %>% 
  arrange(desc(`Date Rated`))

#> # A tibble: 2,229 x 13
#>    Const  `Your Rating` `Date Rated` Title  URL      `Title Type` `IMDb Rating`
#>    <chr>          <int> <date>       <chr>  <chr>    <chr>                <dbl>
#>  1 tt723~             2 2018-04-13   Taxi 5 https:/~ movie                 5.60
#>  2 tt331~             2 2018-04-08   Game ~ https:/~ movie                 5.40
#>  3 tt509~             5 2018-04-08   Dans ~ https:/~ movie                 6.20
#>  4 tt287~             5 2018-04-05   Red S~ https:/~ movie                 6.80
#>  5 tt699~             2 2018-04-04   Gasto~ https:/~ movie                 3.90
#>  6 tt536~             5 2018-03-31   Mary ~ https:/~ movie                 6.00
#>  7 tt167~             6 2018-03-28   Ready~ https:/~ movie                 7.90
#>  8 tt678~             3 2018-03-28   Dolph~ https:/~ movie                 6.40
#>  9 tt641~             2 2018-03-24   Eva    https:/~ movie                 4.90
#> 10 tt714~             5 2018-03-24   La fi~ https:/~ movie                 6.20
#> # ... with 2,219 more rows, and 6 more variables: `Runtime (mins)` <int>,
#> #   Year <int>, Genres <chr>, `Num Votes` <int>, `Release Date` <date>,
#> #   Directors <chr>

• Réordonnez ratings selon les notes IMDb (ÌMDb Rating) et utilisateur (Your Rating).

• Réordonnez ratings selon les titres des films (Title), par ordre alphabétique.

• Réordonnez ratings selon les notes IMDb (ÌMDb Rating) et utilisateur (Your Rating).

  ratings %>% 
    arrange(desc(`ÌMDb Rating`), desc(`Your Rating`))

• Réordonnez ratings selon les titres des films (Title), par ordre alphabétique.

  ratings %>% 
    arrange(Title)

Des aides à la sélection :

• starts_with("abc") : noms commençant par "abc".
• ends_with("xyz") : noms finissant par "xyz".
• contains("ijk") : noms contenant "ijk".
• matches("(.)\\1") : noms correspondant à l'expression régulière.
• num_range("x", 1:3): noms correspondant à x1, x2 et x3.

ratings %>% 
  arrange(desc(`Date Rated`)) %>% 
  select(Title, ends_with("Rating"))

#> # A tibble: 2,229 x 3
#>   Title           `Your Rating` `IMDb Rating`
#>   <chr>                   <int>         <dbl>
#> 1 Taxi 5                      2          5.60
#> 2 Game Over, Man!             2          5.40
#> 3 Dans la brume               5          6.20
#> 4 Red Sparrow                 5          6.80
#> # ... with 2,225 more rows

• Sélectionnez les colonnes contenant "Date".

• Sélectionnez les colonnes contenant "Title", "Your Rating", "Date Rated" et "Release Date" par leu indice.

• Sélectionnez les colonnes contenant "Date".

  ratings %>% 
    select(contains("Date"))

• Sélectionnez les colonnes contenant "Title", "Your Rating", "Date Rated" et "Release Date" par leu indice.

  ratings %>% 
    select(4, 2, 3, 12)

ratings %>% 
  arrange(desc(`Date Rated`)) %>% 
  mutate(Rating_is_better = `Your Rating`>=`IMDb Rating`) %>% 
  select(Title, `Your Rating`, `IMDb Rating`, Rating_is_better)

#> # A tibble: 2,229 x 4
#>    Title            `Your Rating` `IMDb Rating` Rating_is_better
#>    <chr>                    <int>         <dbl> <lgl>           
#>  1 Taxi 5                       2          5.60 F               
#>  2 Game Over, Man!              2          5.40 F               
#>  3 Dans la brume                5          6.20 F               
#>  4 Red Sparrow                  5          6.80 F               
#>  5 Gaston Lagaffe               2          3.90 F               
#>  6 Mary Magdalene               5          6.00 F               
#>  7 Ready Player One             6          7.90 F               
#>  8 Dolphins                     3          6.40 F               
#>  9 Eva                          2          4.90 F               
#> 10 La finale                    5          6.20 F               
#> # ... with 2,219 more rows

• Calculez la moyenne des notes Your Ratinget IMDb Rating.

• Ajoutez les mois month et jours days à partir de Date Rated (package lubridate).

• Calculez la moyenne des notes Your Ratinget IMDb Rating.

  ratings %>% 
    mutate(`Avg Rating` = (`Your Rating`+`IMDb Rating`)/2)

• Ajoutez les mois month et jours days à partir de Date Rated (package lubridate).

  ratings %>% 
    mutate(
      Month = lubridate::month(`Date Rated`),
      day = lubridate::day(`Date Rated`)
    )

read_csv(file = "./materials/ratings.csv") %>%  
  summarise(
    n_movies = n(),
    my_average_rating = mean(`Your Rating`),
    oldest_rating = min(`Date Rated`),
    newest_rating =  max(`Date Rated`)
  )

#> # A tibble: 1 x 4
#>   n_movies my_average_rating oldest_rating newest_rating
#>      <int>             <dbl> <date>        <date>       
#> 1     2229              5.60 2014-11-08    2018-04-13

• Calculez la moyenne, l'écart-type, le minimum et le maximum des notes de IMDb Rating.

• Calculez le nombre de jours séparant la première note de la dernière.

• Calculez la moyenne, l'écart-type, le minimum et le maximum des notes de IMDb Rating.

  ratings %>% 
    summarise(
      mean = mean(`IMDb Rating`),
      sd = sd(`IMDb Rating`),
      min = min(`IMDb Rating`),
      max = max(`IMDb Rating`)
    )

• Calculez le nombre de jours séparant la première note de la dernière.

  ratings %>% 
    summarise(
      ndays = max(`Date Rated`)-min(`Date Rated`)
    )

read_csv(file = "./materials/ratings.csv") %>% 
  mutate(
    Year = lubridate::year(`Date Rated`)
  ) %>% 
  filter(Year!="2014") %>% 
  group_by(Year) %>% 
  summarise(
    n_movies = n(),
    my_average_rating = mean(`Your Rating`),
    users_average_rating = mean(`IMDb Rating`)
  ) %>% 
  arrange(desc(Year))

#> # A tibble: 4 x 4
#>    Year n_movies my_average_rating users_average_rating
#>   <dbl>    <int>             <dbl>                <dbl>
#> 1  2018       52              4.98                 6.42
#> 2  2017      204              5.12                 6.44
#> 3  2016      238              5.13                 6.48
#> 4  2015      374              5.07                 6.19

• Calculez le temps total (Runtime (mins)) et le nombre de films pour l'année 2017, par note (Your Rating). Repartez du fichier CSV et triez le résultat par ordre décroissant de la variable de temps total calculée.

• Calculez le temps total (Runtime (mins)) et le nombre de films pour l'année 2017, par note (Your Rating). Repartez du fichier CSV et triez le résultat par ordre décroissant de la variable de temps total calculée.

  read_csv(file = "./materials/ratings.csv") %>% 
    mutate(
      Year = lubridate::year(`Date Rated`)
    ) %>% 
    filter(Year=="2017") %>% 
    group_by(`Your Rating`) %>% 
    summarise(
      n_movies = n(),
      runtime_all = sum(`Runtime (mins)`)
    ) %>% 
    arrange(des(runtime_all))

• Cheatsheets Rstudio : https://www.rstudio.com/resources/cheatsheets/

• Site de dplyr : dplyr.tidyverse.org

vignette(package = "dplyr")

library(DBI)

library(odbc)

tidyverse_conflicts()

#> -- Conflicts ------------------------------------ tidyverse_conflicts() --
#> x readr::col_factor() masks scales::col_factor()
#> x purrr::discard()    masks scales::discard()
#> x dplyr::filter()     masks stats::filter()
#> x dplyr::lag()        masks stats::lag()
#> x pryr::partial()     masks purrr::partial()
#> x dplyr::src()        masks Hmisc::src()
#> x dplyr::summarize()  masks Hmisc::summarize()

• Lancez PostgreSQL via le raccourci "SQL Shell (psql)".

• Laissez les valeurs par défaut (pressez "Entrée").

Pour retirer le message d'avertissement, ajouter "chcp 1252" dans le fichier ".bat".

• \? pour l'aide des commandes psql.
• \q quitter.
• \h aide des commandes sql.
• \l liste des bases de données.
• \c se connecter à une base.
• \d [nom] pour la description d'une table, d'un index, séquence, vue.
• \d liste des relations (tables, vues et séquences).
• \i nom_fichier.sql exécuter un fichier de commandes SQL.

• Créer un utilisateur.

  CREATE ROLE rdb LOGIN password 'rdbpass';

• Créer une base.

  CREATE DATABASE datawarehouse;

• Modifier le propriétaire d'une base.

  ALTER DATABASE datawarehouse OWNER TO 'rdb';

Des connecteurs :

• RMySQL: MySQL et MariaDB.
• RPostgreSQL: Postgres et Redshift.
• RSQLite: base de données SQLite intégrée.
• odbc: protocoles de connection libre.
• bigrquery: Google BigQuery.

• Identifier le driver de connection.

  odbcListDrivers() %>% 
    filter(attribute=="DriverODBCVer") %>% 
    select(-attribute)

  #>                                                     name      value
  #> 1                                             SQL Server      03.50
  #> 2               Microsoft Access Driver (*.mdb, *.accdb)      02.50
  #> 3 Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)      02.50
  #> 4            Microsoft Access Text Driver (*.txt, *.csv)      02.50
  #> 5                           PostgreSQL ODBC Driver(ANSI) 10.01.0000
  #> 6                        PostgreSQL ODBC Driver(UNICODE) 10.01.0000

• Initier la connection.

  con <- dbConnect(
    drv = odbc(),
    driver = "PostgreSQL ODBC Driver(UNICODE)",
    server = "localhost",
    port = 5432,
    database = "postgres",
    uid = "postgres",
    password = "password"
  )

  con <- dbConnect(
    drv = odbc(),
    driver = "PostgreSQL ODBC Driver(UNICODE)",
    server = "localhost",
    port = 5432,
    database = "postgres",
    uid = "postgres",
    password = rstudioapi::askForPassword("Mot de passe :")
  )

• Créer un utilisateur.

  dbSendQuery(con, SQL("CREATE ROLE test_user LOGIN password 'test_pwd';"))

  #> <OdbcResult>
  #>   SQL  CREATE ROLE test_user LOGIN password 'test_pwd';
  #>   ROWS Fetched: 0 [complete]
  #>        Changed: 71285632

• Créer une base.

  dbSendQuery(con, SQL("CREATE DATABASE test_data;"))

  #> Warning in new_result(connection@ptr, statement): Cancelling previous query

  #> <OdbcResult>
  #>   SQL  CREATE DATABASE test_data;
  #>   ROWS Fetched: 0 [complete]
  #>        Changed: 71285632

• Modifier le propriétaire d'une base.

  dbSendQuery(con, SQL("ALTER DATABASE test_data OWNER TO test_user;"))

  #> Warning in new_result(connection@ptr, statement): Cancelling previous query

  #> <OdbcResult>
  #>   SQL  ALTER DATABASE test_data OWNER TO test_user;
  #>   ROWS Fetched: 0 [complete]
  #>        Changed: 71285632

• Se déconnecter de la base.

  dbDisconnect(con)

  #> Warning in connection_release(conn@ptr): There is a result object still in use.
  #> The connection will be automatically released when it is closed

• Se connecter en tant que "test_user".

  con <- dbConnect(
    drv = odbc(),
    driver = "PostgreSQL ODBC Driver(UNICODE)",
    server = "localhost",
    database = "test_data",
    port = 5432,
    uid = "test_user",
    password = "test_pwd"
  )

• Lister les tables.

  dbListTables(con)

  #> character(0)

• Créer la table mtcars.

  mtcars %>% 
    rownames_to_column(var = "car") %>% 
    mutate(const = gsub(" .*", "", car)) %>% 
    select(const, everything()) %>% 
    dbWriteTable(con, "mtcars", .)
  

• Lister les tables.

  dbListTables(con)

  #> [1] "mtcars"

• Lister les champs d'une table.

  dbListFields(con, "mtcars")

  #>  [1] "const" "car"   "mpg"   "cyl"   "disp"  "hp"    "drat"  "wt"    "qsec" 
  #> [10] "vs"    "am"    "gear"  "carb"

• Lire une table.

  dbReadTable(con, "mtcars") %>% 
    as_tibble()

  #> # A tibble: 32 x 13
  #>   const car     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
  #>   <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
  #> 1 Mazda Mazd~  21.0  6.00   160 110    3.90  2.62  16.5  0     1.00  4.00  4.00
  #> 2 Mazda Mazd~  21.0  6.00   160 110    3.90  2.88  17.0  0     1.00  4.00  4.00
  #> 3 Dats~ Dats~  22.8  4.00   108  93.0  3.85  2.32  18.6  1.00  1.00  4.00  1.00
  #> 4 Horn~ Horn~  21.4  6.00   258 110    3.08  3.22  19.4  1.00  0     3.00  1.00
  #> 5 Horn~ Horn~  18.7  8.00   360 175    3.15  3.44  17.0  0     0     3.00  2.00
  #> # ... with 27 more rows

• Sélectioner les voitures du constructeur Mercedes.

  res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE const='Merc'")
  dbFetch(res)

  #>   const         car  mpg cyl  disp  hp drat   wt qsec vs am gear carb
  #> 1  Merc   Merc 240D 24.4   4 146.7  62 3.69 3.19 20.0  1  0    4    2
  #> 2  Merc    Merc 230 22.8   4 140.8  95 3.92 3.15 22.9  1  0    4    2
  #> 3  Merc    Merc 280 19.2   6 167.6 123 3.92 3.44 18.3  1  0    4    4
  #> 4  Merc   Merc 280C 17.8   6 167.6 123 3.92 3.44 18.9  1  0    4    4
  #> 5  Merc  Merc 450SE 16.4   8 275.8 180 3.07 4.07 17.4  0  0    3    3
  #> 6  Merc  Merc 450SL 17.3   8 275.8 180 3.07 3.73 17.6  0  0    3    3
  #> 7  Merc Merc 450SLC 15.2   8 275.8 180 3.07 3.78 18.0  0  0    3    3

• Libérer les ressources occupées par la requête.

  dbClearResult(res)
  dbDisconnect(con)

• En tant qu'utilisateur "postgres", créez une base "datawarehouse".
  Créez un utilisateur "data_user".
  Changez le propriétaire de "datawarehouse" par "data_user".

• En tant qu'utilisateur "data_user", exportez le fichier "ratings.csv" vers la base. Remplacez les espaces par des underscores dans les noms de colonnes.

• Listez les noms de colonnes de la table créée.

• Utilisez une requête SQL pour compter le nombre de films pour chaque note (Your Rating)
  Importer le résultat localement sous la forme d'un tibble.

• Sélectionnez les films notés entre le 1er janvier 2017 et le 31 décembre 2017.
  Exportez cette table vers la base de données.

• En tant qu'utilisateur "postgres", créez une base "datawarehouse". Créez un utilisateur "data_user".
  Changez le propriétaire de "datawarehouse" par "data_user".

  con <- dbConnect(
    drv = odbc(),
    driver = "PostgreSQL ODBC Driver(UNICODE)",
    server = "localhost",
    database = "postgres",
    port = 5432,
    uid = "postgres",
    password = "password"
  )
  dbSendQuery(con, SQL("CREATE ROLE data_user LOGIN password 'data_pwd';"))
  dbSendQuery(con, SQL("CREATE DATABASE datawarehouse;"))
  dbSendQuery(con, SQL("ALTER DATABASE datawarehouse OWNER TO data_user;"))
  dbDisconnect(con)

• En tant qu'utilisateur "data_user", exportez le fichier "ratings.csv" vers la base. Enlevez les espaces dans les noms de colonnes.

  ratings <- read_csv(file = "./materials/ratings.csv") %>% 
    `colnames<-`(gsub(" ", "", colnames(.)))

  con <- dbConnect(
    drv = odbc(),
    driver = "PostgreSQL ODBC Driver(UNICODE)",
    server = "localhost",
    database = "datawarehouse",
    port = 5432,
    uid = "data_user",
    password = "data_pwd"
  )
  dbWriteTable(con, "ratings", ratings)

• Listez les noms de colonnes de la table créée.

  dbListFields(con, 'ratings')

• Utilisez une requête SQL pour compter le nombre de films pour chaque note ("YourRating").
  Importer le résultat localement sous la forme d'un tibble.

  dbSendQuery(
    conn = con, 
    statement = SQL(
      'SELECT "YourRating", COUNT(*) as count FROM ratings GROUP BY "YourRating";'
    )
  ) %>% 
    dbFetch() %>% 
    as_tibble()

• Sélectionnez les films notés entre le 1er janvier 2017 et le 31 décembre 2017.
  Exportez cette table vers la base de données.

  dbSendQuery(
    conn = con, 
    statement = SQL(
      'SELECT * 
        FROM ratings 
        WHERE "DateRated">=\'2017-01-01\' AND "DateRated"<=\'2017-12-31\';'
    )
  ) %>% 
    dbFetch()%>% 
    dbWriteTable(con, "ratings2017", .)
  
  dbListTables(con)

Liste de diffusion autour du package DBI: r-sig-db.

vignette(package = "DBI")

library(DBI)

library(odbc)

library(tidyverse)

library(dbplyr)

tidyverse_conflicts()

#> -- Conflicts ------------------------------------ tidyverse_conflicts() --
#> x readr::col_factor() masks scales::col_factor()
#> x purrr::discard()    masks scales::discard()
#> x dplyr::filter()     masks stats::filter()
#> x dbplyr::ident()     masks dplyr::ident()
#> x dplyr::lag()        masks stats::lag()
#> x pryr::partial()     masks purrr::partial()
#> x dbplyr::sql()       masks dplyr::sql()
#> x dplyr::src()        masks Hmisc::src()
#> x dplyr::summarize()  masks Hmisc::summarize()

• Connection sur une base de données SQL intégrée (package RSQLite).

  con <- dbConnect(RSQLite::SQLite(), path = ":memory:")

• Connection à la base PostgreSQL.

  con <- dbConnect(
    drv = odbc(),
    driver = "PostgreSQL ODBC Driver(UNICODE)",
    server = "localhost",
    database = "datawarehouse",
    port = 5432,
    uid = "data_user",
    password = "data_pwd"
  )

• Remplir la base.

  ratings <- read_csv(file = "./materials/ratings.csv") %>% 
    `colnames<-`(gsub(" ", "", colnames(.))) %>% 
    mutate(Year = lubridate::year(DateRated))

  copy_to(
    dest = con, 
    df = ratings, 
    name = "ratings_idx",
    temporary = FALSE, 
    indexes = list(
      "DateRated", 
      "YourRating", 
      "Title", 
      "Year"
    ),
    overwrite = TRUE
  )

Note : temporary = TRUE permet de rendre la table uniquement disponible à la connection en cours et uniquement pendant celle-ci.

• Faire une référence à une table depuis une connection.

  ratings_db <- tbl(con, "ratings")
  class(ratings_db)

  #> [1] "tbl_dbi"  "tbl_sql"  "tbl_lazy" "tbl"

• Afficher l'objet.

  ratings_db

  #> # Source:   table<ratings> [?? x 13]
  #> # Database: postgres [data_user@localhost:/datawarehouse]
  #>   Const  YourRating DateRated  Title URL   TitleType IMDbRating `Runtime(mins)`
  #>   <chr>       <int> <date>     <chr> <chr> <chr>          <dbl>           <int>
  #> 1 tt100~          5 2014-12-17 He's~ http~ movie           6.40             129
  #> 2 tt100~          7 2014-11-08 Mega~ http~ movie           7.30              95
  #> 3 tt010~          3 2014-11-26 The ~ http~ movie           5.10              90
  #> 4 tt100~          7 2014-12-26 Ca$h  http~ movie           6.00             100
  #> # ... with more rows, and 5 more variables: Year <int>, Genres <chr>,
  #> #   NumVotes <int>, ReleaseDate <date>, Directors <chr>

• Faire des requêtes sans SQL.

  date_sevendaysago <- (Sys.Date()-7)
  
  ratings_db %>% 
    filter(DateRated > date_sevendaysago) %>% 
    select(Title, DateRated, YourRating, IMDbRating)

  #> # Source:   lazy query [?? x 4]
  #> # Database: postgres [data_user@localhost:/datawarehouse]
  #> # ... with 4 variables: Title <chr>, DateRated <date>, YourRating <int>,
  #> #   IMDbRating <dbl>

• Faire des opérations en SQL sans SQL.

  ratings_db %>% 
    group_by(Year) %>% 
    summarise(
      N = n(),
      AvgRating = mean(YourRating)
    ) %>% 
    arrange(Year)

  #> Warning: Missing values are always removed in SQL.
  #> Use `AVG(x, na.rm = TRUE)` to silence this warning

  #> # Source:     lazy query [?? x 3]
  #> # Database:   postgres [data_user@localhost:/datawarehouse]
  #> # Ordered by: Year
  #>    Year N               AvgRating
  #>   <int> <S3: integer64>     <dbl>
  #> 1  1936 1                    8.00
  #> 2  1937 1                    7.00
  #> 3  1940 1                    7.00
  #> 4  1941 1                    7.00
  #> # ... with more rows

• dplyr et tbl_df.

  ratings_summary <- ratings %>% 
    group_by(Year) %>% 
    summarise(
      N = n(),
      AvgRating = mean(YourRating)
    ) %>% 
    arrange(Year)

  str(ratings_summary, 1)

  #> Classes 'tbl_df', 'tbl' and 'data.frame':    5 obs. of  3 variables:
  #>  $ Year     : num  2014 2015 2016 2017 2018
  #>  $ N        : int  1361 374 238 204 52
  #>  $ AvgRating: num  5.93 5.07 5.13 5.12 4.98

• dplyr et tbl_sql.

  ratings_summary_db <- ratings_db %>% 
    group_by(Year) %>% 
    summarise(
      N = n(),
      AvgRating = mean(YourRating)
    ) %>% 
    arrange(Year)

  str(ratings_summary_db, 1)

  #> List of 2
  #>  $ src:List of 2
  #>   ..- attr(*, "class")= chr [1:3] "src_dbi" "src_sql" "src"
  #>  $ ops:List of 4
  #>   ..- attr(*, "class")= chr [1:3] "op_arrange" "op_single" "op"
  #>  - attr(*, "class")= chr [1:4] "tbl_dbi" "tbl_sql" "tbl_lazy" "tbl"

• L'envoi de la requête ne s'effectue qu'à la fin ou à l'affichage de l'objet.

  ratings_summary_db %>% 
    print()

  #> Warning: Missing values are always removed in SQL.
  #> Use `AVG(x, na.rm = TRUE)` to silence this warning

  #> # Source:     lazy query [?? x 3]
  #> # Database:   postgres [data_user@localhost:/datawarehouse]
  #> # Ordered by: Year
  #>    Year N               AvgRating
  #>   <int> <S3: integer64>     <dbl>
  #> 1  1936 1                    8.00
  #> 2  1937 1                    7.00
  #> 3  1940 1                    7.00
  #> 4  1941 1                    7.00
  #> # ... with more rows

• Les données ne sont jamais téléchargées dans R (sauf mention explicite).

  ratings_summary_db %>% 
    collect()

  #> Warning: Missing values are always removed in SQL.
  #> Use `AVG(x, na.rm = TRUE)` to silence this warning

  #> # A tibble: 66 x 3
  #>    Year N               AvgRating
  #>   <int> <S3: integer64>     <dbl>
  #> 1  1936 1                    8.00
  #> 2  1937 1                    7.00
  #> 3  1940 1                    7.00
  #> 4  1941 1                    7.00
  #> # ... with 62 more rows

• Que fait dplyr pour effectuer la commande précédente ratings_summary_db ?

   ratings_summary_db %>% 
    show_query()

  #> Warning: Missing values are always removed in SQL.
  #> Use `AVG(x, na.rm = TRUE)` to silence this warning

  #> <SQL>
  #> SELECT "Year", COUNT(*) AS "N", AVG("YourRating") AS "AvgRating"
  #> FROM "ratings"
  #> GROUP BY "Year"
  #> ORDER BY "Year"

• Traduire du R en requête SQL.

  translate_sql(head(ratings_db))

  #> <SQL> HEAD("ratings_db")

• Comment dbplyr traduit les fonctions as.numeric() et as.character() ?

• Comment dbplyr traduit l'opérateur ^ ?

• Comment dbplyr traduit les fonctions mean()et sd() ?
  Et avecna.rm = TRUE ?

• Comment dbplyr traduit les fonctions as.numeric() et as.character() ?

  translate_sql(as.numeric(x))

  #> <SQL> CAST("x" AS NUMERIC)

  translate_sql(as.character(x))

  #> <SQL> CAST("x" AS TEXT)

• Comment dbplyr traduit l'opérateur ^ ?

  translate_sql(x^2)

  #> <SQL> POWER("x", 2.0)

  tbl(con, sql('select 1 as x')) %>%
    mutate(sqr = x^2)

  #> # Source:   lazy query [?? x 2]
  #> # Database: postgres [data_user@localhost:/datawarehouse]
  #>       x   sqr
  #>   <int> <dbl>
  #> 1     1  1.00

• Comment dbplyr traduit les fonctions mean()et sd() ?
  Et avec trim = 0.05 ou na.rm = TRUE ?

  translate_sql(mean(x))

  #> Warning: Missing values are always removed in SQL.
  #> Use `avg(x, na.rm = TRUE)` to silence this warning

  #> <SQL> avg("x") OVER ()

  translate_sql(mean(x, trim = 0.05))

  #> Error in mean(x, trim = 0.05): argument inutilisé (trim = 0.05)

  translate_sql(mean(x, na.rm = TRUE))

  #> <SQL> avg("x") OVER ()

• Rappatrier les données d'une requête via collect()

  ratings_summary <- ratings_summary_db %>% 
    collect()

  #> Warning: Missing values are always removed in SQL.
  #> Use `AVG(x, na.rm = TRUE)` to silence this warning

  class(ratings_summary)

  #> [1] "tbl_df"     "tbl"        "data.frame"

• Connaître les dimensions de l'objet avant collect() ?

  tally(ratings_summary_db)

  #> Warning: Missing values are always removed in SQL.
  #> Use `AVG(x, na.rm = TRUE)` to silence this warning

  #> # Source:   lazy query [?? x 1]
  #> # Database: postgres [data_user@localhost:/datawarehouse]
  #>   n              
  #>   <S3: integer64>
  #> 1 66

• Utilisez les fonctions head() , tail() et nrow() sur l'une des tables exportées.
  Que se passe-t'il et pourquoi ?

• Utilisez les fonctions head() , tail() et nrow() sur l'une des tables exportées.
  Que se passe-t'il et pourquoi ?

  ratings_summary_db %>% head(1)

  #> # Source:     lazy query [?? x 3]
  #> # Database:   postgres [data_user@localhost:/datawarehouse]
  #> # Ordered by: Year
  #>    Year N               AvgRating
  #>   <int> <S3: integer64>     <dbl>
  #> 1  1936 1                    8.00

  ratings_summary_db %>% tail()

  #> Error: tail() is not supported by sql sources

  ratings_summary_db %>% nrow()

  #> [1] NA

explain(ratings_summary_db)

#> Warning: Missing values are always removed in SQL.
#> Use `AVG(x, na.rm = TRUE)` to silence this warning

#> <SQL>
#> SELECT "Year", COUNT(*) AS "N", AVG("YourRating") AS "AvgRating"
#> FROM "ratings"
#> GROUP BY "Year"
#> ORDER BY "Year"

#> 

#> Warning: Missing values are always removed in SQL.
#> Use `AVG(x, na.rm = TRUE)` to silence this warning

#> <PLAN>
#> Sort  (cost=91.70..92.20 rows=200 width=44)
#>   Sort Key: ratings."Year"
#>   ->  HashAggregate  (cost=79.56..82.06 rows=200 width=44)
#>         Group Key: ratings."Year"
#>         ->  Seq Scan on ratings  (cost=0.00..67.32 rows=1632 width=8)

• Schéma "partiel" des tables du package nycflights13.

• Connectez vous à la base "datawarehouse" en tant que "rdb".

• Créez une table "mtcars" avec le jeu de données mtcars.
  Comparez les fonctions dplyr::copy_to() (en utilisant l'argument temporary) et DBI::dbWriteTable().

• Modifiez mtcars en passant les noms de lignes dans une colonne et en ajoutant une colonne constructeur const.
  Exporter l'objet mtcars vers la base.
  Que se passe-t'il ? (argument overwrite).

• Créez dans la base "datawarehouse" l'ensemble des tables de nycflights13.
  Vérifiez que les tables sont bien créées.

• Effectuez la jointure de planeset flights pour identifier les 3 constructeurs présentant le plus grand nombre d'heures de vol (?dplyr::join).
  Quelle est la requête SQL réalisée ?

• Connectez vous à la base "datawarehouse" en tant que "rdb".

  con <- dbConnect(
    drv = odbc(),
    driver = "PostgreSQL ODBC Driver(UNICODE)",
    server = "localhost",
    database = "datawarehouse",
    port = 5432,
    uid = "data_user",
    password = "data_pwd"
  )

• Créez une table "mtcars" avec le jeu de données mtcars.
  Comparez les fonctions dplyr::copy_to() (en utilisant l'argument temporary) et DBI::dbWriteTable().

  copy_to(con, mtcars, "mtcars", temporary = TRUE, overwrite = TRUE)
  dbListTables(con)

  #> [1] "ratings"     "ratings2017" "ratings_idx"

  copy_to(con, mtcars, "mtcars", temporary = FALSE, overwrite = TRUE)
  dbListTables(con)

  #> [1] "mtcars"      "ratings"     "ratings2017" "ratings_idx"

  dbWriteTable(con, "mtcars", mtcars, overwrite = TRUE)
  dbListTables(con)

  #> [1] "mtcars"      "ratings"     "ratings2017" "ratings_idx"

• Modifiez mtcars en passant les noms de lignes dans une colonne et en ajoutant une colonne constructeur const.
  Exporter l'objet mtcars vers la base.
  Que se passe-t'il ? (argument overwrite).

  mtcars %>% 
    rownames_to_column(var = "car") %>% 
    mutate(const = gsub(" .*", "", car)) %>% 
    select(const, everything()) %>% 
    dbWriteTable(con, "mtcars", ., overwrite = TRUE)

• Créez dans la base "datawarehouse" l'ensemble des tables de nycflights13.
  Vérifiez que les tables sont bien créées.

  library(nycflights13)
  dbWriteTable(con, "flights", flights)
  dbWriteTable(con, "airports", airports)
  dbWriteTable(con, "planes", planes)
  dbWriteTable(con, "weather", weather)
  dbWriteTable(con, "airlines", airlines)
  
  dbListTables(con)

• Créez dans la base "datawarehouse" l'ensemble des tables de nycflights13.
  Vérifiez que les tables sont bien créées.

  library(nycflights13)
  nycflights13_db <- ls("package:nycflights13") %>% 
    lapply(., function(df) { 
      copy_to(
        df = get(df), 
        dest = con, 
        name = df, 
        overwrite = TRUE, 
        temporary = TRUE
      ) 
    }) %>% 
    `names<-`(ls("package:nycflights13"))

  dbListTables(con)

  #> [1] "mtcars"      "ratings"     "ratings2017" "ratings_idx"

• Effectuez la jointure de planeset flights pour identifier les 3 constructeurs présentant le plus grand nombre d'heures de vol (?dplyr::join).

  longest_air_time_db <- nycflights13_db[["flights"]] %>%
    inner_join(nycflights13_db[["planes"]], by = "tailnum") %>%
    group_by(manufacturer) %>% 
    summarise(total_air_time = sum(air_time)/60) %>% 
    arrange(desc(total_air_time)) %>%
    select(manufacturer, total_air_time) %>% 
    head(3)
  longest_air_time_db

  #> # Source:     lazy query [?? x 2]
  #> # Database:   postgres [data_user@localhost:/datawarehouse]
  #> # Ordered by: desc(total_air_time)
  #>   manufacturer     total_air_time
  #>   <chr>                     <dbl>
  #> 1 BOEING                   296118
  #> 2 AIRBUS                   153993
  #> 3 AIRBUS INDUSTRIE          96442

• Effectuez la jointure de planeset flights pour identifier les 3 constructeurs présentant le plus grand nombre d'heures de vol (?dplyr::join).
  Quelle est la requête SQL réalisée ?

  longest_air_time_db %>% show_query()

  #> <SQL>
  #> SELECT "manufacturer", "total_air_time"
  #> FROM (SELECT *
  #> FROM (SELECT "manufacturer", SUM("air_time") / 60.0 AS "total_air_time"
  #> FROM (SELECT "TBL_LEFT"."year" AS "year.x", "TBL_LEFT"."month" AS "month", "TBL_LEFT"."day" AS "day", "TBL_LEFT"."dep_time" AS "dep_time", "TBL_LEFT"."sched_dep_time" AS "sched_dep_time", "TBL_LEFT"."dep_delay" AS "dep_delay", "TBL_LEFT"."arr_time" AS "arr_time", "TBL_LEFT"."sched_arr_time" AS "sched_arr_time", "TBL_LEFT"."arr_delay" AS "arr_delay", "TBL_LEFT"."carrier" AS "carrier", "TBL_LEFT"."flight" AS "flight", "TBL_LEFT"."tailnum" AS "tailnum", "TBL_LEFT"."origin" AS "origin", "TBL_LEFT"."dest" AS "dest", "TBL_LEFT"."air_time" AS "air_time", "TBL_LEFT"."distance" AS "distance", "TBL_LEFT"."hour" AS "hour", "TBL_LEFT"."minute" AS "minute", "TBL_LEFT"."time_hour" AS "time_hour", "TBL_RIGHT"."year" AS "year.y", "TBL_RIGHT"."type" AS "type", "TBL_RIGHT"."manufacturer" AS "manufacturer", "TBL_RIGHT"."model" AS "model", "TBL_RIGHT"."engines" AS "engines", "TBL_RIGHT"."seats" AS "seats", "TBL_RIGHT"."speed" AS "speed", "TBL_RIGHT"."engine" AS "engine"
  #>   FROM "flights" AS "TBL_LEFT"
  #>   INNER JOIN "planes" AS "TBL_RIGHT"
  #>   ON ("TBL_LEFT"."tailnum" = "TBL_RIGHT"."tailnum")
  #> ) "zlzjmmynwb"
  #> GROUP BY "manufacturer") "otzkpogcwa"
  #> ORDER BY "total_air_time" DESC) "bgjtdarjkv"
  #> LIMIT 3

• Calculez la moyenne des températures temp (table weather) par station origin et par année year.
  Comparez le résultat de la version locale (tbl_df) et distante (tbl_sql).

• Comptez le nombre d'aéroport dont le code dest (table flights) commence par la lettre "A" (Wildcard SQL; %like%).

• Créez une nouvelle colonne origin_dest à partir de origin et dest.

• Calculez la moyenne des températures temp (table weather) par station origin et par année year.
  Comparez le résultat de la version locale (tbl_df) et distante (tbl_sql).

  nycflights13::weather %>% 
    group_by(origin, year) %>%  
    summarise(avg_temp = mean(temp))

  #> # A tibble: 3 x 3
  #> # Groups:   origin [?]
  #>   origin  year avg_temp
  #>   <chr>  <dbl>    <dbl>
  #> 1 EWR     2013     NA  
  #> 2 JFK     2013     54.4
  #> 3 LGA     2013     55.7

  nycflights13_db[["weather"]] %>% 
    group_by(origin, year) %>% 
    summarise(avg_temp = mean(temp))

• Calculez la moyenne des températures temp (table weather) par station origin et par année year.
  Comparez le résultat de la version locale (tbl_df) et distante (tbl_sql).

  nycflights13::weather %>% 
    group_by(origin, year) %>%  
    summarise(avg_temp = mean(temp))

  nycflights13_db[["weather"]] %>% 
    group_by(origin, year) %>% 
    summarise(avg_temp = mean(temp))

  #> # Source:   lazy query [?? x 3]
  #> # Database: postgres [data_user@localhost:/datawarehouse]
  #> # Groups:   origin
  #>   origin  year avg_temp
  #>   <chr>  <dbl>    <dbl>
  #> 1 JFK     2013     54.4
  #> 2 EWR     2013     55.5
  #> 3 LGA     2013     55.7
  #> # ... with more rows

• Comptez le nombre d'aéroport dont le code dest (table flights) commence par la lettre "A" (Wildcard SQL; %like%).

  nycflights13_db[["flights"]] %>% 
    filter(dest %like% 'A%') %>% 
    summarise(count = n_distinct(dest))

  #> # Source:   lazy query [?? x 1]
  #> # Database: postgres [data_user@localhost:/datawarehouse]
  #>   count          
  #>   <S3: integer64>
  #> 1 7

• Créez une nouvelle colonne origin_dest à partir de origin et dest.

  nycflights13_db[["flights"]] %>%
    mutate(origin_dest = origin %|| '-' ||% dest) %>%
    select(origin, dest, origin_dest)

  #> # Source:   lazy query [?? x 3]
  #> # Database: postgres [data_user@localhost:/datawarehouse]
  #>   origin dest  origin_dest
  #>   <chr>  <chr> <chr>      
  #> 1 EWR    IAH   EWR-IAH    
  #> 2 LGA    IAH   LGA-IAH    
  #> # ... with more rows

  nycflights13_db[["flights"]] %>%
    mutate(origin_dest = CONCAT(origin, "-", dest)) %>%
    select(origin, dest, origin_dest)

  nycflights13_db[["flights"]] %>%
    mutate(origin_dest = paste(origin, dest, sep = "-")) %>%
    select(origin, dest, origin_dest)

• Créez une nouvelle colonne origin_dest à partir de origin et dest.

  nycflights13_db[["flights"]] %>%
    mutate(origin_dest = origin %|| '-' ||% dest) %>%
    select(origin, dest, origin_dest)

  nycflights13_db[["flights"]] %>%
    mutate(origin_dest = CONCAT(origin, "-", dest)) %>%
    select(origin, dest, origin_dest)

  #> # Source:   lazy query [?? x 3]
  #> # Database: postgres [data_user@localhost:/datawarehouse]
  #>   origin dest  origin_dest
  #>   <chr>  <chr> <chr>      
  #> 1 EWR    IAH   EWR-IAH    
  #> 2 LGA    IAH   LGA-IAH    
  #> # ... with more rows

  nycflights13_db[["flights"]] %>%
    mutate(origin_dest = paste(origin, dest, sep = "-")) %>%
    select(origin, dest, origin_dest)

• Créez une nouvelle colonne origin_dest à partir de origin et dest.

  nycflights13_db[["flights"]] %>%
    mutate(origin_dest = origin %|| '-' ||% dest) %>%
    select(origin, dest, origin_dest)

  nycflights13_db[["flights"]] %>%
    mutate(origin_dest = CONCAT(origin, "-", dest)) %>%
    select(origin, dest, origin_dest)

  nycflights13_db[["flights"]] %>%
    mutate(origin_dest = paste(origin, dest, sep = "-")) %>%
    select(origin, dest, origin_dest)

  #> # Source:   lazy query [?? x 3]
  #> # Database: postgres [data_user@localhost:/datawarehouse]
  #>   origin dest  origin_dest
  #>   <chr>  <chr> <chr>      
  #> 1 EWR    IAH   EWR-IAH    
  #> 2 LGA    IAH   LGA-IAH    
  #> # ... with more rows

• Une base de données contenant des schémas décrivant des tables.

  dbSendQuery(con, "CREATE SCHEMA nycflights13;")
  dbWriteTable(con, SQL("nycflights13.flights"), flights)

  #> <OdbcResult>
  #>   SQL  CREATE SCHEMA nycflights13;
  #>   ROWS Fetched: 0 [complete]
  #>        Changed: 71285632

  

• La fonction tbl() cherche dans le schéma par défaut ("public" sur PostgreSQL).

  tbl(con, "mtcars") %>% head()

  #> # Source:   lazy query [?? x 13]
  #> # Database: postgres [data_user@localhost:/datawarehouse]
  #>   const car     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
  #>   <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
  #> 1 Mazda Mazd~  21.0  6.00   160 110    3.90  2.62  16.5  0     1.00  4.00  4.00
  #> 2 Mazda Mazd~  21.0  6.00   160 110    3.90  2.88  17.0  0     1.00  4.00  4.00
  #> 3 Dats~ Dats~  22.8  4.00   108  93.0  3.85  2.32  18.6  1.00  1.00  4.00  1.00
  #> 4 Horn~ Horn~  21.4  6.00   258 110    3.08  3.22  19.4  1.00  0     3.00  1.00
  #> 5 Horn~ Horn~  18.7  8.00   360 175    3.15  3.44  17.0  0     0     3.00  2.00
  #> # ... with more rows

• dplyr::ìn_schema() permet de spécifier le schéma à utiliser.

  tbl(con, in_schema("public", "mtcars")) %>% head()

  #> # Source:   lazy query [?? x 13]
  #> # Database: postgres [data_user@localhost:/datawarehouse]
  #>   const car     mpg   cyl  disp    hp  drat    wt  qsec    vs    am  gear  carb
  #>   <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
  #> 1 Mazda Mazd~  21.0  6.00   160 110    3.90  2.62  16.5  0     1.00  4.00  4.00
  #> 2 Mazda Mazd~  21.0  6.00   160 110    3.90  2.88  17.0  0     1.00  4.00  4.00
  #> 3 Dats~ Dats~  22.8  4.00   108  93.0  3.85  2.32  18.6  1.00  1.00  4.00  1.00
  #> 4 Horn~ Horn~  21.4  6.00   258 110    3.08  3.22  19.4  1.00  0     3.00  1.00
  #> 5 Horn~ Horn~  18.7  8.00   360 175    3.15  3.44  17.0  0     0     3.00  2.00
  #> # ... with more rows

• Accéder à une table présente dans un schéma autre que celui par défaut.

  tbl(con, in_schema("nycflights13", "flights")) %>% head(1)

  #> # Source:   lazy query [?? x 19]
  #> # Database: postgres [data_user@localhost:/datawarehouse]
  #>    year month   day dep_time sched_dep_time dep_delay arr_time sched_arr_time
  #>   <int> <int> <int>    <int>          <int>     <dbl>    <int>          <int>
  #> 1  2013     1     1      517            515      2.00      830            819
  #> # ... with 11 more variables: arr_delay <dbl>, carrier <chr>, flight <int>,
  #> #   tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
  #> #   hour <dbl>, minute <dbl>, time_hour <dttm>

  tbl(con, in_schema("nycflights13", "weather")) %>% head(1)

  #> # Source:   lazy query [?? x 15]
  #> # Database: postgres [data_user@localhost:/datawarehouse]
  #>   origin  year month   day  hour  temp  dewp humid wind_dir wind_speed
  #>   <chr>  <dbl> <dbl> <int> <int> <dbl> <dbl> <dbl>    <dbl>      <dbl>
  #> 1 EWR     2013  1.00     1     0  37.0  21.9  54.0      230       10.4
  #> # ... with 5 more variables: wind_gust <dbl>, precip <dbl>, pressure <dbl>,
  #> #   visib <dbl>, time_hour <dttm>

• Tutoriel SQL: www.codecademy.com

• Le SQL en 10 étapes : blog.jooq.org

• "Databases using R" : db.rstudio.com

• Identifier les balises HTML encapsulant l'information.
• Utiliser l'architecture en arbre du code HTML.

• Identifier les balises HTML encapsulant l'information.
• Utiliser l'architecture en arbre du code HTML et du CSS.

library(xml2)
library(rvest)

library(tidyverse)

tidyverse_conflicts()

#> -- Conflicts ------------------------------------ tidyverse_conflicts() --
#> x readr::col_factor()     masks scales::col_factor()
#> x purrr::discard()        masks scales::discard()
#> x dplyr::filter()         masks stats::filter()
#> x rvest::guess_encoding() masks readr::guess_encoding()
#> x rvest::html()           masks Hmisc::html()
#> x dbplyr::ident()         masks dplyr::ident()
#> x dplyr::lag()            masks stats::lag()
#> x pryr::partial()         masks purrr::partial()
#> x rvest::pluck()          masks purrr::pluck()
#> x dbplyr::sql()           masks dplyr::sql()
#> x dplyr::src()            masks Hmisc::src()
#> x dplyr::summarize()      masks Hmisc::summarize()

1. Télécharger le code HTML et le convertir en XML.

   read_html()

2. Extraire les noeuds.

   html_nodes()

3. Extraire le contenu des noeuds.
   • html_text()
   • html_name()
   • html_attrs()
   • html_children()
   • html_table()

   imdb_xml %>% 
     html_nodes("h1") %>% 
     html_text()

• Récupérer l'URL de la page casting du film.

  rpo_cast_url <- "https://www.imdb.com/title/tt1677720/fullcredits/"

• Récupérer la table contenant les acteurs et les personnages du film.

  rpo_cast_list <- read_html(rpo_cast_url) %>% 
    html_nodes("table.cast_list")

• Récupérer la liste des personnages du film.

  rpo_character_list <- rpo_cast_list %>% 
    html_nodes("td.character")

• Extraire le contenu des noeuds.

  html_text(rpo_character_list) %>% head(2)

  #> [1] "\n              \n            Parzival /  \n            Wade \n                      \n\n              \n          "   
  #> [2] "\n              \n            Art3mis /  \n            Samantha \n                      \n\n              \n          "

• Formater le contenu.

  rpo_character_list %>% 
    html_text() %>% 
    gsub("\n", "", .) %>% 
    gsub(" +", " ", .) %>% 
    gsub("^ (.*) $", "\\1", .) %>% 
    head(2)

  #> [1] "Parzival / Wade"    "Art3mis / Samantha"

• Reconstruction du tableau acteur/personnage.

  cast_tbl <- tibble(
    Actor = read_html(rpo_cast_url) %>% 
      html_nodes("td.itemprop") %>% 
      html_nodes("span.itemprop") %>% 
      html_text(),
    Character = rpo_character_list %>% 
      html_text() %>% 
      gsub("\n", "", .) %>% 
      gsub(" +", " ", .) %>% 
      gsub("^ (.*) $", "\\1", .)
  )
  
  cast_tbl %>% 
    head(3)

  #> # A tibble: 3 x 2
  #>   Actor          Character         
  #>   <chr>          <chr>             
  #> 1 Tye Sheridan   Parzival / Wade   
  #> 2 Olivia Cooke   Art3mis / Samantha
  #> 3 Ben Mendelsohn Sorrento

• Lecture du fichier contenant les notes des films.

  ratings <- read_csv("materials/ratings.csv") %>% 
    `colnames<-`(gsub(" ", "", colnames(.))) %>% 
    mutate(
      Year = lubridate::year(DateRated),
      Month = lubridate::month(DateRated),
      Day = lubridate::wday(DateRated)
    )

• Création d'un nouveau utilisateur et d'une nouvelle base.

  con <- dbConnect(
    drv = odbc(),
    driver = "PostgreSQL ODBC Driver(UNICODE)",
    server = "localhost",
    database = "postgres",
    port = 5432,
    uid = "postgres",
    password = "password"
  )
  dbSendQuery(con, SQL("CREATE ROLE imdb_user LOGIN password 'imdb_pwd';"))
  dbSendQuery(con, SQL("CREATE DATABASE movies;"))
  dbSendQuery(con, SQL("ALTER DATABASE movies OWNER TO imdb_user;"))
  dbDisconnect(con)

• Connection avec "imdb_user" sur "movies".

  con <- dbConnect(
    drv = odbc(),
    driver = "PostgreSQL ODBC Driver(UNICODE)",
    server = "localhost",
    database = "movies",
    port = 5432,
    uid = "imdb_user",
    password = "imdb_pwd"
  )

• Création de la table "ratings" (et d'un objet tbl_sql).

  ratings_db <- copy_to(
    dest = con, 
    df = ratings, 
    names = "ratings", 
    overwrite = TRUE,
    temporary = FALSE
  )

• Extraction du casting d'un film provenant de la table "ratings".

  cast_list <- ratings_db %>% 
    filter(Title %like% "Ready Player One") %>% 
    collect() %>% 
    .[["URL"]] %>% 
    paste0(., "fullcredits/") %>% 
    read_html() %>% 
    html_nodes("table.cast_list")

• Création d'un tableau casting.

  cast_tbl <- tibble(
    Actor = cast_list %>% 
      html_nodes("td.itemprop") %>% 
      html_nodes("span.itemprop") %>% 
      html_text(),
    Character = cast_list %>% 
      html_nodes("td.character") %>% 
      html_text() %>% 
      gsub("\n", "", .) %>% 
      gsub(" +", " ", .) %>% 
      gsub("^ (.*) $", "\\1", .)
  )

• Fontion de récuppération du casting d'un film.

  get_cast <- function(x) {
    x %>% 
      paste0(., "fullcredits/") %>% 
      read_html() %>% 
      html_nodes("table.cast_list") %>% 
      (function(node) {
        cast_tbl <- tibble(
          URL = x,
          Actor = node %>% 
            html_nodes("td.itemprop") %>% 
            html_nodes("span.itemprop") %>% 
            html_text(),
          Character = node %>% 
            html_nodes("td.character") %>%
            html_text() %>% 
            gsub("\n", "", .) %>% 
            gsub(" +", " ", .) %>% 
            gsub("^ (.*) $", "\\1", .)
        )
      }) %>% 
      return()
  }

• Fontion de récuppération du casting d'un film.

  get_cast("https://www.imdb.com/title/tt0100240/")

  #> # A tibble: 36 x 3
  #>   URL                                   Actor             Character
  #>   <chr>                                 <chr>             <chr>    
  #> 1 https://www.imdb.com/title/tt0100240/ Jonathan Brandis  Bastian  
  #> 2 https://www.imdb.com/title/tt0100240/ Kenny Morrison    Atreyu   
  #> 3 https://www.imdb.com/title/tt0100240/ Clarissa Burt     Xayide   
  #> 4 https://www.imdb.com/title/tt0100240/ John Wesley Shipp Barney   
  #> 5 https://www.imdb.com/title/tt0100240/ Martin Umbach     Nimbly   
  #> # ... with 31 more rows

• Ajout du casting dans ratings_db (local).

  casting <- ratings_db %>% 
    select(URL) %>% 
    collect() %>% 
    head() %>% 
    mutate(
      Casting = map(URL, get_cast)
    )
  casting

  #> # A tibble: 6 x 2
  #>   URL                                   Casting           
  #>   <chr>                                 <list>            
  #> 1 https://www.imdb.com/title/tt1001508/ <tibble [121 x 3]>
  #> 2 https://www.imdb.com/title/tt1001526/ <tibble [19 x 3]> 
  #> 3 https://www.imdb.com/title/tt0100240/ <tibble [36 x 3]> 
  #> 4 https://www.imdb.com/title/tt1002966/ <tibble [43 x 3]> 
  #> 5 https://www.imdb.com/title/tt0100403/ <tibble [65 x 3]> 
  #> # ... with 1 more row

En utilisant le lien url : https://www.imdb.com/user/ur56341222/ratings.

• Reconstituez une table contenant au minimum : le titre, le genre, la durée et la note (étoile jaune).

• Compilez des statistiques (moyenne, écart-type, etc.) dans une nouvelle table.

• Exportez ces tables vers une base de données.

• Reconstituez une table contenant au minimum : le titre, le genre, la durée et la note (étoile jaune).

  movies_list <- "https://www.imdb.com/user/ur56341222/ratings" %>% 
    read_html() %>% 
    html_nodes("div#ratings-container.lister-list") %>% 
    html_nodes("div.lister-item.mode-detail")

  • Titre.

  title = x %>% 
    html_nodes("h3.lister-item-header") %>% 
    html_text() %>% 
    gsub("\n", "", .) %>% 
    gsub(" [0-9]+.", "", .) %>% 
    gsub(" +", " ", .) %>% 
    gsub("^ (.*) $", "\\1", .)

• Reconstituez une table contenant au minimum : le titre, le genre, la durée et la note (étoile jaune).

  movies_list <- "https://www.imdb.com/user/ur56341222/ratings" %>% 
    read_html() %>% 
    html_nodes("div#ratings-container.lister-list") %>% 
    html_nodes("div.lister-item.mode-detail")

  • Genres.

  genres = x %>% 
    html_nodes("span.genre") %>% 
    html_text() %>% 
    gsub("\n", "", .) %>% 
    gsub(" +", " ", .) %>% 
    gsub("^ (.*) $", "\\1", .)

• Reconstituez une table contenant au minimum : le titre, le genre, la durée et la note (étoile jaune).

  movies_list <- "https://www.imdb.com/user/ur56341222/ratings" %>% 
    read_html() %>% 
    html_nodes("div#ratings-container.lister-list") %>% 
    html_nodes("div.lister-item.mode-detail")

  • Durée du film.

  runtime = x %>% 
    html_nodes("span.runtime") %>% 
    html_text() %>% 
    gsub("\n", "", .) %>% 
    gsub(" +", " ", .) %>% 
    gsub("^ (.*) $", "\\1", .)

• Reconstituez une table contenant au minimum : le titre, le genre, la durée et la note (étoile jaune).

  movies_list <- "https://www.imdb.com/user/ur56341222/ratings" %>% 
    read_html() %>% 
    html_nodes("div#ratings-container.lister-list") %>% 
    html_nodes("div.lister-item.mode-detail")

  • Note.

  rating = x %>% 
    html_nodes("div.ipl-rating-widget") %>% 
    html_nodes("div.ipl-rating-star.small") %>% 
    html_text("span.ipl-rating-star__rating") %>% 
    unique() %>% 
    gsub("\n", "", .) %>% 
    gsub(" +", " ", .) %>% 
    gsub("^ (.*) $", "\\1", .) %>% 
    as.numeric() %>% 
    (function(.x) {if (length(.x)!=2) {c(NA, NA)} else {.x}})

• Reconstituez une table contenant au minimum : le titre, le genre, la durée et la note (étoile jaune).

  get_movies_info <- function(x) {
    c(
      x %>% html_nodes("h3.lister-item-header") %>% html_text() %>% 
        gsub("\n", "", .) %>% gsub(" [0-9]+.", "", .) %>% 
        gsub(" +", " ", .) %>% gsub("^ (.*) $", "\\1", .),
  
      x %>% html_nodes("span.genre") %>% html_text() %>% 
        gsub("\n", "", .) %>% gsub(" +", " ", .) %>% gsub("^ (.*) $", "\\1", .),
  
      x %>% html_nodes("span.runtime") %>% html_text() %>% 
        gsub("\n", "", .) %>% gsub(" +", " ", .) %>% 
        gsub("^ (.*) $", "\\1", .) %>% ifelse(length(.)==0, NA, .),
  
      x %>% html_nodes("div.ipl-rating-widget") %>% 
        html_nodes("div.ipl-rating-star.small") %>% 
        html_text("span.ipl-rating-star__rating") %>% 
        gsub("\n", "", .) %>% gsub(" +", " ", .) %>% gsub("^ (.*) $", "\\1", .) %>% 
        as.numeric() %>%(function(.x) {if (length(.x)!=2) {c(NA, NA)} else {.x}})
    ) %>% 
      `names<-`(c("title", "genres", "runtime", "imdbrating", "userating")) %>% 
      return()
  }

• Reconstituez une table contenant au minimum : le titre, le genre, la durée et la note (étoile jaune).

  movies_list <- "https://www.imdb.com/user/ur56341222/ratings" %>% 
    read_html() %>% 
    html_nodes("div#ratings-container.lister-list") %>% 
    html_nodes("div.lister-item.mode-detail") %>% 
    lapply(., get_movies_info) %>% 
    do.call("bind_rows", .)

• nodbi sur GitHub : nodbi

Supporte :

• MongoDB
• Redis
• CouchDB
• Elasticsearch
• etcd
• Riak

• Package sur le CRAN :
  • sofa (CouchDB) CRAN.
  • mongolite (MongoDB) CRAN.
  • elastic (Elasticsearch) CRAN.
  • etseed (etcd) CRAN.
• Package sur hors CRAN :
  • RedisAPI (Redis) GitHub.
  • rrlite (Redis) GitHub.